<?
class factura_servicio{

	// Propiedades
	var $id;
	var $id_zona;
	var $id_persona;
	var $letra_per;
	var $cedula_per;
	var $nombre_per;
	var $direccion_per;
	var $telefono_per;
	var $factura;
	var $control;
	var $fecha;
	var $descripcion;
	var $estatus;

	var $detalles;

	var $total;
	

	function get($conn, $id){
		try {
			$q = "SELECT A.*, B.letra AS letra_per, B.cedula AS cedula_per, B.nombre AS nombre_per, B.direccion AS direccion_per, B.telefono AS telefono_per, lpad(A.factura::text,7,'0') AS factura FROM facturas_servicio AS A LEFT JOIN personas AS B ON A.id_persona = B.id WHERE A.id=$id";
			//die($q);
			$r = $conn->Execute(utf8_encode($q));
			if(!$r->EOF){
				$this->id = $r->fields['id'];
				$this->id_zona = $r->fields['id_zona'];
				$this->id_persona = $r->fields['id_persona'];
				$this->letra_per = $r->fields['letra_per'];
				$this->cedula_per = $r->fields['cedula_per'];
				$this->nombre_per = $r->fields['nombre_per'];
				$this->direccion_per = $r->fields['direccion_per'];
				$this->telefono_per = $r->fields['telefono_per'];
				$this->factura = $r->fields['factura'];
				$this->control = $r->fields['control'];
				$this->fecha = $r->fields['fecha'];
				$this->descripcion = $r->fields['descripcion'];
				$this->estatus = $r->fields['estatus'];
				
				$q = "SELECT A.id_concepto, B.nombre, A.monto, A.iva, A.descripcion FROM facturas_servicio_detalles AS A INNER JOIN conceptos AS B ON A.id_concepto = B.id WHERE id_facturas_servicio=$id";
				//die($q);
				$rCF = $conn->Execute(utf8_encode($q));
				$i=0;
				$this->monto_pagos=0;

				while(!$rCF->EOF){
					$DetallesAux[$i][0] = $rCF->fields['id_concepto'];
					$DetallesAux[$i][1] = $rCF->fields['nombre'];
					$DetallesAux[$i][2] = $rCF->fields['monto'];
					$DetallesAux[$i][3] = $rCF->fields['iva'];
					$DetallesAux[$i][4] = $rCF->fields['descripcion'];

 
					$i++;
					$rCF->movenext();
				}
				$this->detalles = new Services_JSON();
				$this->detalles = is_array($DetallesAux) ? $this->detalles->encode($DetallesAux) : false;

				
			}
			 
		}
		catch( ADODB_Exception $e ){
			if($e->getCode()==-1)
				return ERROR_CATCH_VFK;
			elseif($e->getCode()==-5)
				return ERROR_CATCH_VUK;
			else
				return ERROR_CATCH_GENERICO;
		}
	}
	
	function get_all($conn, $orden="A.id DESC", $Tipo, $Valor, $TipoE, $max=10, $from=1){
		try {
			$zona = $_SESSION['SesionZona'];
			$TipoE = empty($TipoE) ? 0 : $TipoE;
			if(empty($Valor)){
			$q = "SELECT A.id FROM facturas_servicio AS A WHERE id_zona = $zona ";
			$q.= "ORDER BY $orden ";
			}
			elseif($Tipo==0){
			$q = "SELECT A.id FROM facturas_servicio AS A ";
			$q.= "WHERE A.id::text ILIKE '%$Valor%'::text AND id_zona = $zona ";
			$q.= "ORDER BY $orden ";
			}
			elseif($Tipo==1){
			$q = "SELECT A.id FROM facturas_servicio AS A ";
			$q.= "WHERE A.factura::text ILIKE '%$Valor%'::text AND A.id_zona = $zona ";
			$q.= "ORDER BY $orden ";
			}
			elseif($Tipo==2){
			$q = "SELECT A.id FROM facturas_servicio AS A ";
			$q.= "INNER JOIN personas AS B ON A.id_persona = B.id ";
			$q.= "WHERE B.cedula::text ILIKE '%$Valor%'::text AND A.id_zona = $zona ";
			$q.= "ORDER BY $orden ";
			}
			elseif($Tipo==3){
			$q = "SELECT A.id FROM facturas_servicio AS A ";
			$q.= "INNER JOIN personas AS B ON A.id_persona = B.id ";
			$q.= "WHERE B.nombre::text ILIKE '%$Valor%'::text AND A.id_zona = $zona ";
			$q.= "ORDER BY $orden ";
			}
			
			$r = ($max!=0) ? $conn->SelectLimit($q, $max, $from) : $conn->Execute(utf8_encode($q));
			$coleccion=array();
			while(!$r->EOF){
				$ue = new factura_servicio;
				$ue->get($conn, $r->fields['id']);
				$coleccion[] = $ue;
				$r->movenext();
			}
			$this->total = $r->RecordCount();
			return $coleccion;
		}
		catch( ADODB_Exception $e ){
			if($e->getCode()==-1)
				return ERROR_CATCH_VFK;
			elseif($e->getCode()==-5)
				return ERROR_CATCH_VUK;
			else
				return ERROR_CATCH_GENERICO;
		}
	}
	
	function __destruct(){
    }
	
	function add($conn, $id_zona, $factura, $control, $fecha, $descripcion, $estatus,  $cedula_per, $nombre_per, $direccion_per, $telefono_per, $detalles, $letra_per){
		try {
			/*
			$q = "SELECT (COALESCE(MAX(factura),0) + 1) AS factura FROM facturas_servicio WHERE id_zona=$id_zona";
			$r = $conn->Execute(utf8_encode($q));
			$numero = $r->fields['factura'];


			$q = "SELECT (COALESCE(MAX(control),0) + 1) AS control FROM facturas_servicio WHERE id_zona=$id_zona";
			$r = $conn->Execute(utf8_encode($q));
			$cont = $r->fields['control'];
			*/
			//Verificar Existencia Persona
			$opersona = new persona;

			$q = "SELECT * FROM personas WHERE letra= '$letra_per' AND cedula = '$cedula_per'";
			$r = $conn->Execute(utf8_encode($q));
			if (!$r->EOF) {
				$opersona->set($conn, $r->fields['id'], $cedula_per, $nombre_per, $direccion_per, $telefono_per, $letra_per);
				$id_per = $r->fields['id'];
			} else {
				$opersona->add($conn, $cedula_per, $nombre_per, $direccion_per, $telefono_per, $letra_per);
				$q = "SELECT * FROM personas WHERE letra='$letra_per' AND cedula = '$cedula_per'";
				$r = $conn->Execute(utf8_encode($q));
				if (!$r->EOF) {
					$id_per = $r->fields['id'];
				}

			}



			$q = "INSERT INTO facturas_servicio ";
			$q.= "(id_zona, id_persona, fecha, descripcion, estatus) ";
			$q.= "VALUES ";
			$q.= "($id_zona, NULLIF($id_per,-1), '$fecha', upper('$descripcion'), upper('$estatus')) ";
			//$q.= "RETURNING id";
			
			//die($q);

			$r = $conn->Execute(utf8_encode($q));
			
			$this->auditoria($conn, $q);

			$q2 = "SELECT MAX(id) AS id FROM facturas_servicio";
			$r2 = $conn->Execute(utf8_encode($q2));
			//$this->GuardarDetalles($conn, $r->fields['id'],$detalles);
			$this->GuardarDetalles($conn, $r2->fields['id'],$detalles);
			return REG_ADD_OK;
		}
		catch( ADODB_Exception $e ){
			if($e->getCode()==-1)
				return ERROR_CATCH_VFK;
			elseif($e->getCode()==-5)
				return ERROR_CATCH_VUK;
			else
				return ERROR_CATCH_GENERICO;
		}
	}

	function set($conn, $id, $id_zona, $factura, $control, $fecha, $descripcion, $estatus, $cedula_per, $nombre_per, $direccion_per, $telefono_per, $detalles, $letra_per){
		try {

			//Verificar Existencia Persona
			$opersona = new persona;

			$q = "SELECT * FROM personas WHERE letra = '$letra_per' AND cedula = '$cedula_per'";

			$r = $conn->Execute(utf8_encode($q));
			if (!$r->EOF) {
				$opersona->set($conn, $r->fields['id'], $cedula_per, $nombre_per, $direccion_per, $telefono_per, $letra_per);
				$id_per = $r->fields['id'];
			} else {
				$opersona->add($conn, $cedula_per, $nombre_per, $direccion_per, $telefono_per, $letra_per);
				$q = "SELECT * FROM personas WHERE letra = '$letra_per' AND cedula = '$cedula_per'";
				$r = $conn->Execute(utf8_encode($q));
				if (!$r->EOF) {
					$id_per = $r->fields['id'];
				}

			}

			$q = "UPDATE facturas_servicio ";
			$q.= "SET id_zona = $id_zona, id_persona=$id_per, fecha='$fecha', descripcion=upper('$descripcion'), estatus = upper('$estatus') ";
			$q.= "WHERE id = $id";		
	
			//die($q);
			$r = $conn->Execute(utf8_encode($q));
			
			$this->auditoria($conn, $q);

			$this->GuardarDetalles($conn, $id,$detalles);

			return REG_SET_OK;

		}
		catch( ADODB_Exception $e ){
			if($e->getCode()==-1)
				return ERROR_CATCH_VFK;
			elseif($e->getCode()==-5)
				return ERROR_CATCH_VUK;
			else
				return ERROR_CATCH_GENERICO;
		}
	}

	function del($conn, $id){
		try {
			$q = "DELETE FROM facturas_servicio WHERE id = $id";
			//die($q);
			$conn->Execute(utf8_encode($q));
			
			$this->auditoria($conn, $q);
			
			return REG_DEL_OK;
		}
		catch( ADODB_Exception $e ){
			if($e->getCode()==-1)
				return ERROR_CATCH_VFK;
			elseif($e->getCode()==-5)
				return ERROR_CATCH_VUK;
			else
				return ERROR_CATCH_GENERICO;
		}
	}

	function GuardarDetalles($conn,$id,$detalles){
		try {
			$JsonRec = new Services_JSON();
			$JsonRec=$JsonRec->decode(str_replace("\\","",$detalles));
			//die($JsonRec);
			$q = "DELETE FROM facturas_servicio_detalles WHERE id_facturas_servicio=$id";
			$r = $conn->Execute(utf8_encode($q));
			
			$this->auditoria($conn, $q);
			
			if(is_array($JsonRec->detalles)){
				foreach($JsonRec->detalles as $detallesAux){
					$id_concepto = $detallesAux[0];
					$monto = guardafloat($detallesAux[2]);
					$iva = guardafloat($detallesAux[3]);
					$descripcion = $detallesAux[4];

					$q = "INSERT INTO facturas_servicio_detalles ";
					$q.= "(id_facturas_servicio, id_concepto, monto, iva, descripcion) ";
					$q.= "VALUES ";
					$q.= "($id,$id_concepto, $monto, $iva, upper('$descripcion')) ";
					//die($q);
					$conn->Execute(utf8_encode($q));
					
					$this->auditoria($conn, $q);
				}
			}
		}
		catch( ADODB_Exception $e ){
			if($e->getCode()==-1)
				return ERROR_CATCH_VFK;
			elseif($e->getCode()==-5)
				return ERROR_CATCH_VUK;
			else
				return ERROR_CATCH_GENERICO;
		}
	}


	function total_registro_busqueda($conn,$Tipo, $Valor, $TipoE, $orden="A.id")
	{
		$zona = $_SESSION['SesionZona'];
		$TipoE = empty($TipoE) ? 0 : $TipoE;
			if(empty($Valor)){
			$q = "SELECT count(*) AS total FROM facturas_servicio AS A WHERE id_zona = $zona ";
			}
			elseif($Tipo==0){
			$q = "SELECT count(*) AS total FROM facturas_servicio AS A ";
			$q.= "WHERE A.id::text ILIKE '%$Valor%'::text AND id_zona = $zona ";
			}
			elseif($Tipo==1){
			$q = "SELECT count(*) AS total FROM facturas_servicio AS A ";
			$q.= "WHERE A.factura::text ILIKE '%$Valor%'::text AND A.id_zona = $zona ";
			}
			elseif($Tipo==2){
			$q = "SELECT count(*) AS total FROM facturas_servicio AS A ";
			$q.= "INNER JOIN personas AS B ON A.id_persona = B.id ";
			$q.= "WHERE B.cedula::text ILIKE '%$Valor%'::text AND A.id_zona = $zona ";
			}
			elseif($Tipo==3){
			$q = "SELECT count(*) AS total FROM facturas_servicio AS A ";
			$q.= "INNER JOIN personas AS B ON A.id_persona = B.id ";
			$q.= "WHERE B.nombre::text ILIKE '%$Valor%'::text AND A.id_zona = $zona ";
			}

		$r = $conn->Execute(utf8_encode($q));
		$total = $r->fields['total'];
		return $total;
	}	

	function auditoria($conn, $query) {
		$id_usuario = $_SESSION['id'];
		$q = "INSERT INTO auditoria(id_usuario, query) VALUES($id_usuario,$$" . $query . "$$);";
		//die($q);
		$conn->Execute(utf8_encode($q));

		return true;
	}		
	
}
?>
